define erp='erp_mli2.';
define outils='outils_mli2.';



--CALL uvse_ExistsOrCreate('PAR_MODREG');



DELETE FROM  se_PAR_MODREG ;


--SELECT * FROM uv_creglements
--select *  from se_PAR_MODREG
--select * from &erp.PAR_MODREG


INSERT INTO se_PAR_MODREG
 (SELECT ID modreg_cod,
         NULL doc_cod,
         0 enreg_id,
         'N' enreg_s,
         libelle modreg_lib,
         null modreg_lib2,
         null modregabr_lib,
         'T' tiers_typ,
         NULL traite_lib,
         Nvl(EDITRT,'N') editrt_ok,
         NULL c_jourenc_nb,
         'N' affacturage_ok,
         NULL modregedi_cod
     FROM uv_creglements fuv) ;

 UPDATE se_PAR_MODREG tf
 SET enreg_id = (select enreg_id FROM &erp.PAR_MODREG f WHERE f.MODREG_COD=tf.MODREG_COD )
 WHERE EXISTS (SELECT 1 FROM &erp.PAR_MODREG f WHERE f.MODREG_COD=tf.MODREG_COD);

 UPDATE se_PAR_MODREG SET enreg_id=&outils.enreg_id.NEXTVAL WHERE enreg_id IS NULL OR enreg_id =0;

 CALL uvse_synchroverserp('modreg_cod','PAR_MODREG','O') ;



 commit;

 
select * from (
SELECT &outils.stragg(source) source,modreg_cod, doc_cod, enreg_id, enreg_s, modreg_lib, modreg_lib2, modregabr_lib, tiers_typ, traite_lib, editrt_ok, c_jourenc_nb, affacturage_ok, modregedi_cod
FROM (
  SELECT 'uv' source ,g.* FROM se_PAR_MODREG g
  UNION ALL
  SELECT 'se' source ,g.* FROM &erp.PAR_MODREG g
  )
GROUP BY modreg_cod, doc_cod, enreg_id, enreg_s, modreg_lib, modreg_lib2, modregabr_lib, tiers_typ, traite_lib, editrt_ok, c_jourenc_nb, affacturage_ok, modregedi_cod
HAVING Count(1)!=2) r
ORDER BY modreg_cod